using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Collections;

using IDAL;
using Entities;

namespace SQLDBDAL
{
    class AccountDAO : IAccountDAO
    {
        // Remember that Roles and SecretQuestions are fixed       
        private const string SQL_SELECT_ACCOUNT = "SELECT * FROM Account";

        private const string SQL_CHECK_ACCOUNT = "SELECT Username FROM Account WHERE Username=@Username";

        private const string SQL_SELECT_ACCOUNT_BY_USERNAME = "SELECT * FROM Account WHERE Username=@Username";

        private const string SQL_SELECT_QUESTION_BY_USERNAME = "SELECT SecretQuestion FROM Account WHERE Username=@Username";

        private const string SQL_SELECT_PASSWORD_BY_ANSWER = "SELECT Password FROM Account WHERE SecretAnswer=@SecretAnswer";

        private const string SQL_SELECT_ACCOUNT_ROLE_BY_USERNAME = "SELECT Username, Password, Role, Discount FROM Account WHERE Username=@Username AND Password=@Password";

        private const string SQL_COUNT_ORDER_BY_USERNAME = "SELECT COUNT(OrderID) FROM Orders WHERE Username=@Username";

        private const string SQL_INSERT_ACCOUNT = "INSERT INTO Account(Username, Password, SecretQuestion, SecretAnswer, Role, Discount) VALUES (@Username, @Password, @SecretQuestion, @SecretAnswer, @Role, 0)";

        private const string SQL_UPDATE_ACCOUNT = "UPDATE Account SET Password=@Password, Role=@Role WHERE Username=@Username";

        private const string SQL_UPDATE_PASSWORD = "UPDATE Account SET Password=@Password WHERE Username=@Username";

        private const string SQL_UPDATE_DISCOUNT = "UPDATE Account SET Discount=@Discount WHERE Username=@Username";

        private const string SQL_UPDATE_DISCOUNT_AND_ROLE = "UPDATE Account SET Role=@Role, Discount=@Discount WHERE Username=@Username";

        private const string SQL_DELETE_ACCOUNT = "DELETE FROM Account WHERE Username=@Username";

        private const string SQL_DELETE_ACCOUNT_FROM_CUSTOMER = "DELETE FROM Customers WHERE Username=@Username";

        private const string PARAM_USER_NAME = "@Username";
        private const string PARAM_PASSWORD = "@Password";
        private const string PARAM_SECRET_QUESTION = "@SecretQuestion";
        private const string PARAM_SECRET_ANSWER = "@SecretAnswer";
        private const string PARAM_DISCOUNT = "@Discount";
        private const string PARAM_ROLE = "@Role";

        private const string PROCEDURE_DELETE_ACCOUNT = "sp_DeleteAccount";

        #region IAccountDAO Members

        public bool Delete(AccountInfo acc)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar) };
            parms[0].Value = acc.Username;
            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.StoredProcedure, PROCEDURE_DELETE_ACCOUNT, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public IList<AccountInfo> GetAll()
        {
            List<AccountInfo> accounts = new List<AccountInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_ACCOUNT, null))
            {
                while (reader.Read())
                {
                    AccountInfo acc = new AccountInfo();
                    acc.Username = reader.GetString(0);
                    acc.Password = reader.GetString(1);
                    acc.SecretQuestion = reader.GetString(2);
                    acc.SecretAnswer = reader.GetString(3);
                    acc.Role = reader.GetString(4);
                    if(!reader.IsDBNull(5))
                        acc.Discount = (float)reader.GetDouble(5);
                    accounts.Add(acc);
                }
            }
            return accounts;
        }

        public AccountInfo GetByUserName(string username)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar) };
            if (!string.IsNullOrEmpty(username))
                parms[0].Value = username;
            else
                parms[0].Value = "";

            AccountInfo accInfo = new AccountInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_ACCOUNT_BY_USERNAME, parms))
            {
                while (reader.Read())
                {
                    accInfo.Username = reader.GetString(0);
                    accInfo.Password = reader.GetString(1);
                    accInfo.SecretQuestion = reader.GetString(2);
                    accInfo.SecretAnswer = reader.GetString(3);
                    accInfo.Role = reader.GetString(4);
                    accInfo.Discount = (float)reader.GetDouble(5);
                }
            }
            return accInfo;
        }

        public bool Insert(AccountInfo acc)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_PASSWORD, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_SECRET_QUESTION, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_SECRET_ANSWER, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_ROLE, SqlDbType.Char) };
            parms[0].Value = acc.Username;
            parms[1].Value = acc.Password;
            parms[2].Value = acc.SecretQuestion;
            parms[3].Value = acc.SecretAnswer;
            parms[4].Value = acc.Role;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_INSERT_ACCOUNT, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public bool Update(AccountInfo acc)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARAM_PASSWORD, SqlDbType.NVarChar),
                new SqlParameter(PARAM_ROLE, SqlDbType.Char),   
                new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar)};

            parms[0].Value = acc.Password;
            parms[1].Value = acc.Role;
            parms[2].Value = acc.Username;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_ACCOUNT, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public bool UpdatePassword(AccountInfo acc)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_PASSWORD, SqlDbType.NVarChar), 
                                                        new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar)};

            parms[0].Value = acc.Password;
            parms[1].Value = acc.Username;
            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_PASSWORD, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public bool UpdateDiscount(string discount, string username)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_DISCOUNT, SqlDbType.Float),
                                                        new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar)};

            parms[0].Value = float.Parse(discount);
            parms[1].Value = username;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_DISCOUNT, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public bool UpdateDiscountAndRole(string role, string discount, string username)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_ROLE, SqlDbType.Char),
                                                        new SqlParameter(PARAM_DISCOUNT, SqlDbType.Float),
                                                        new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar)};

            parms[0].Value = role;
            parms[1].Value = float.Parse(discount);
            parms[2].Value = username;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_DISCOUNT_AND_ROLE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public bool CheckExistAccount(string username)
        {
            bool result = false;
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar) };
            parms[0].Value = username;


            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_CHECK_ACCOUNT, parms))
            {
                if (reader.Read())
                {
                    result = true;
                }
            }
            return result;
        }

        public ArrayList getRightUsers(string username, string password)
        {
            ArrayList arr = new ArrayList();
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar),
                                         new SqlParameter(PARAM_PASSWORD, SqlDbType.NVarChar)};
            parms[0].Value = username;
            parms[1].Value = password;


            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_ACCOUNT_ROLE_BY_USERNAME, parms))
            {
                while (reader.Read())
                {
                    string uid = reader.GetString(0);
                    string pwd = reader.GetString(1);
                    string role = reader.GetString(2);
                    float discount = (float)reader.GetDouble(3);

                    arr.Add(uid);
                    //not need password
                    //arr.Add(pwd);
                    arr.Add(role);
                    arr.Add(discount);
                }
            }
            return arr;
        }

        public string GetQuestion(string username)
        {
            string result = null;
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARAM_USER_NAME, SqlDbType.NVarChar) };
            parms[0].Value = username;


            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_QUESTION_BY_USERNAME, parms))
            {
                while (reader.Read())
                {
                    result = reader.GetString(0);
                }
            }
            return result;
        }
        public string CheckAnswer(string answer)
        {
            string result = null;
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARAM_SECRET_ANSWER, SqlDbType.NVarChar) };
            parms[0].Value = answer;


            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_PASSWORD_BY_ANSWER, parms))
            {
                while (reader.Read())
                {
                    result = reader.GetString(0);
                }
            }
            return result;
        }
        #endregion
    }
}
